package dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import model.userModel;
import util.dataUtil;

	public class userdao {
	public static ArrayList<userModel> getdatauser(int id) {
		String sql = "SELECT * FROM Sys_user WHERE id=?";
		
		ArrayList<userModel> modelid = new ArrayList<userModel>();
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				userModel model = new userModel();
				model.setId(rs.getInt("id"));
				model.setIdrole(rs.getInt("idrole"));
				model.setUserName(rs.getString("username"));
				model.setPassWprd(rs.getString("password"));
				model.setFirstName(rs.getString("firstname"));
				model.setLastName(rs.getString("lastname"));
				model.setSex(rs.getInt("sex"));
				model.setBirth_date(rs.getString("birth_date"));
				model.setAddress(rs.getString("address"));
				model.setStatus(rs.getInt("status"));
				model.setLast_update(rs.getString("last_updated"));
				modelid.add(model);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return modelid;
	}
	//search user
	public static ArrayList<userModel> getDataSearchUser(String name) {
		String sql = "SELECT * FROM Sys_user where lastname like '%"+name+"%' or firstname like '%"+name+"%'";
		
		ArrayList<userModel> modelid = new ArrayList<userModel>();
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				userModel model = new userModel();
				model.setId(rs.getInt("id"));
				model.setIdrole(rs.getInt("idrole"));
				model.setUserName(rs.getString("username"));
				model.setPassWprd(rs.getString("password"));
				model.setFirstName(rs.getString("firstname"));
				model.setLastName(rs.getString("lastname"));
				model.setSex(rs.getInt("sex"));
				model.setBirth_date(rs.getString("birth_date"));
				model.setAddress(rs.getString("address"));
				model.setStatus(rs.getInt("status"));
				model.setLast_update(rs.getString("last_updated"));
				modelid.add(model);
			}
		}
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
		return modelid;
	}
	
	//get name user
	public static String getName(int id) {
		String sql = "SELECT * FROM Sys_user WHERE id=?";
				
				String name = "";
				try {
					PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
					ps.setInt(1, id);
					ResultSet rs = ps.executeQuery();
					while (rs.next()) {
						name = rs.getString("firstname")+" "+rs.getString("lastname");
					}
				}
				catch (Exception e) 
				{
					e.printStackTrace();
				}
				
				return name;
			}
	
	public static ArrayList getAlluser() {
		ArrayList listuser = new ArrayList();
		String sql = "SELECT * FROM Sys_user ORDER BY ID";
		try {
			PreparedStatement ps = dataUtil.getconnConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				userModel model = new userModel();
				model.setId(rs.getInt("ID"));
				model.setIdrole(rs.getInt("IDrole"));
				model.setUserName(rs.getString("userName"));
				model.setPassWprd(rs.getString("passWord"));
				model.setFirstName(rs.getString("firstname"));
				model.setLastName(rs.getString("lastName"));
				model.setSex(rs.getInt("sex"));
				model.setBirth_date(rs.getString("birth_Date"));
				model.setAddress(rs.getString("address"));
				model.setStatus(rs.getInt("status"));
				model.setLast_update(rs.getString("last_Updated"));
				listuser.add(model);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return listuser;
	}
	//update user
	public static int updateuser(userModel model) {
		int result = 0;
		try {		
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_UpdateUser(?,?,?,?,?,?,?,?,?,?,?,?)}");

			csmt.setInt("ID", model.getId());
			csmt.setInt("IDRole", model.getIdrole());
			csmt.setString("UserName", model.getUserName());
			csmt.setString("Password", model.getPassWprd());
			csmt.setString("FIRSTNAME", model.getFirstName());
			csmt.setString("LASTNAME", model.getLastName());
			csmt.setInt("Sex", model.getSex());
			csmt.setString("Birth_Date", model.getBirth_date());
			csmt.setString("Address", model.getAddress());
			csmt.setInt("Status", model.getStatus());
			//SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
			//String d = sdf.format( model.getLast_update());
			csmt.setString("Last_updated", model.getLast_update());
			csmt.registerOutParameter("RESULT", java.sql.Types.INTEGER);

			csmt.execute();

			
			result = csmt.getInt("RESULT");
		} catch (SQLException e) {
		result = 0;
		}
		return result;
		}
	//delete user
	public static int deleteUser(userModel model) {
		int result = 0;
		try {
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call SP_DELETEUSER(?,?)}");
			csmt.setInt("ID", model.getId());
			csmt.registerOutParameter("RESULT", java.sql.Types.INTEGER);
			csmt.execute();
			result = csmt.getInt("RESULT");
		} catch (SQLException e) {
		result = 0;
		}
		return result;
			
		}
	//add user
	public static int InsertUser(userModel model) {
		int result = 0;
		try {

			
			CallableStatement csmt = dataUtil.getconnConnection().prepareCall("{call sp_InsertUser(?,?,?,?,?,?,?,?,?,?,?)}");
			csmt.setInt("IDRole", model.getIdrole());
			csmt.setString("UserName", model.getUserName());
			csmt.setString("Password", model.getPassWprd());
			csmt.setString("FIRSTNAME", model.getFirstName());
			csmt.setString("LASTNAME", model.getLastName());
			csmt.setInt("Sex", model.getSex());
			csmt.setString("Birth_Date", model.getBirth_date());
			csmt.setString("Address", model.getAddress());
			csmt.setInt("Status", model.getStatus());
			csmt.setString("Last_updated", model.getLast_update());
			csmt.registerOutParameter("RESULT", java.sql.Types.INTEGER);

			csmt.execute();

			
			result = csmt.getInt("RESULT");
		} catch (SQLException e) {
		result = 0;
		e.printStackTrace();
		
		}
		return result;
		}
}
